﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class DatabaseCheckCompatibilityLevel : IssueTypeCheckBase
    {
        public DatabaseCheckCompatibilityLevel()
        {
            IssueTypeId = 3006;
        }

        public override void Check()
        {
            try
            {
                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                Issue issue;

                // This rule checks that all databases on the server are running under the latest compatibility mode.
                /*
                 * SQL Server Edition 	Database Compatibility Level 	Internal Database Version Number
                       SQL Server  7 	                70 	                        515
                       SQL Server 2000 	                80              	        539
                       SQL Server 2005 	                90 	                        611/612
                       SQL Server 2008 	                100 	                    655
                       SQL Server 2008 R2 	            105 	                    660
                 * 
                 */
                var qry = from d in dc.Databases
                          where
                              (d.Service.ProductVersion.StartsWith("9.0") && (d.CompatibilityLevel == "70" || d.CompatibilityLevel == "80"))

                                 ||

                              (d.Service.ProductVersion.StartsWith("10.0") && ( d.CompatibilityLevel == "80"))

                                 ||

                              (d.Service.ProductVersion.StartsWith("10.50") && ( d.CompatibilityLevel == "80"))
                              ||

                                             (d.Service.ProductVersion.StartsWith("11") && ( d.CompatibilityLevel == "90"))
                                             && d.SourceDatabaseId == null
                          select d;
                //Get Issuetype object  And Create a new  Global issue object 
                IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);


                // Iterate through the Linq Query 
                foreach (var item in qry)
                {
                    int databaseId = item.DatabaseID;
                    //Get Issuetype object

                    issue = new Issue();


                    issue.IssueType = issueType;
                    issue.TargetId = item.DatabaseID;
                    issue.Description = issueType.Description.Replace("{Database}", item.DatabaseName).Replace("{CompatibilityLevel}", item.CompatibilityLevel);
                    dc.Issues.InsertOnSubmit(issue);
                }

                dc.SubmitChanges();

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
    }
}
